FuturePositionRecordV5

V8 Message Definiton

FuturePositionRecords are live risk records that contain start-of-day positions and all subsequent executions, including executions reported as done away.\nThese records are published by a CoreRiskServer and represent the position and risk markup detail for a single futures contract.\nNew records are published immediately when a position changes and about once per minute if no position has changed.\nNote that all stock, future and option records for a chain are published simultaneously and records for the same chain should have consistent marks.

METADATA

Attribute	Value
Topic	4740-risk-v5
MLink Token	ClientRisk
Product	SRRisk
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
fkey_at	enum - AssetType	PRI	'None'
fkey_ts	enum - TickerSrc	PRI	'None'
fkey_tk	VARCHAR(12)	PRI	''
fkey_yr	SMALLINT UNSIGNED	PRI	0
fkey_mn	TINYINT UNSIGNED	PRI	0
fkey_dy	TINYINT UNSIGNED	PRI	0
accnt	VARCHAR(16)	PRI	''
tradeDate	DATE	PRI	'1900-01-01'
riskSession	enum - RiskSession	PRI	'Regular'
clientFirm	VARCHAR(16)	PRI	''	SR assigned client firm
riskServerCode	VARCHAR(6)		''
aggGroup	VARCHAR(16)		''	SR assigned aggregation group
ticker_at	enum - AssetType		'None'	underlying ticker
ticker_ts	enum - TickerSrc		'None'	underlying ticker
ticker_tk	VARCHAR(12)		''	underlying ticker
underliersPerCn	INT		0	number of underlying units per futures contract
underlierType	enum - UnderlierType		'None'
tickValue	FLOAT		0	NLV value of a single tick change in display premium pointValue tickValue tickSize
pointValue	FLOAT		0	NLV value of a single point change in display premium pointValue tickValue tickSize
pointCurrency	enum - Currency		'None'
priceFormat	enum - PriceFormat		'None'	price display format code
futPrc	DOUBLE		0	current future price any market session persists if market closedhalted
futBid	DOUBLE		0	current future bid any market session zero if market closedhalted
futAsk	DOUBLE		0	current future ask any market session zero if market closedhalted
futMark	DOUBLE		0	current mark usually mid market freezes at SR CloseMarkTime
futMarkErrCodes	VARCHAR(255)		'None'
futMarkSource	enum - UMarkSource		'None'
futOpnMidMark	DOUBLE		0	start of day SR mark
futOpnClrMark	DOUBLE		0	start of day clearing mark usually exchange settlement mark
futOpnPosPrv	INT		0	startofday SR contract position rotated from prior day record
futOpnPosClr	INT		0	startofday CKR contract position supplied by clientclearing firm via clearing position load
futOpnPos	INT		0	start of period contract position effective can be from either CLR or SR
futOpnPosSrc	enum - PositionSource		'None'	start of period position source
cnBot	INT		0	number of contracts bot today
cnSld	INT		0	number of contracts sld today
cnBotTrd	INT		0	number of contracts bot today
cnSldTrd	INT		0	number of contracts sld today
cnBotHdg	INT		0	number of contracts bot today from open pos hedging spdrSourceHedgeTool
cnSldHdg	INT		0	number of contracts sld today from open pos hedging spdrSourceHedgeTool
cnBotTrh	INT		0	number of contracts bot today from open pos hedging spdrSourceTradeHedge
cnSldTrh	INT		0	number of contracts sld today from open pos hedging spdrSourceTradeHedge
cnBotOpn	INT		0	number of contracts bot today from open pos hedging spdrSourceOpenHedge
cnSldOpn	INT		0	number of contracts sld today from open pos hedging spdrSourceOpenHedge
cnOpened	INT		0	number of contracts opened today
cnClosed	INT		0	number of contracts closed today
futMnyBot	DOUBLE		0	sum of settle cash for all buy executions
futMnySld	DOUBLE		0	sum of settle cash for all sell executions
dayPnl	FLOAT		0
opnPnlMidMark	FLOAT		0
opnPnlClrMark	FLOAT		0
beta	FLOAT		0	beta usually beta to SPX see AccountConfigbetaSource if applicable
betaSource	enum - BetaSource		'None'
days	SMALLINT		0	days to expiration
rate	FLOAT		0	global rate to expiration
years	FLOAT		0	years to expiration
rh	FLOAT		0	rho dPrc dRate only for STIR futures
ve	FLOAT		0	vega dPrc dVol only for VIX futures
ratePr	FLOAT		0	start of period global rate
yearsPr	FLOAT		0	start of period years to expiration
marginUDn	FLOAT		0	Aggregate RiskSlide uPrc dn
marginUUp	FLOAT		0	Aggregate RiskSlide uPrc up
numExecutions	INT		0	number of included SpdrParentExecution records
maxExecDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	maximum activity dttm of execution records included in this future risk record
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
fkey_tk	1
fkey_yr	2
fkey_mn	3
fkey_dy	4
fkey_at	5
fkey_ts	6
accnt	7
tradeDate	8
riskSession	9
clientFirm	10

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRRisk`.`MsgFuturePositionRecordV5` (
    `fkey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `fkey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `fkey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `fkey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `fkey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `fkey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `tradeDate` DATE NOT NULL DEFAULT '1900-01-01',
    `riskSession` ENUM('Regular','PostClose') NOT NULL DEFAULT 'Regular',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned client firm',
    `riskServerCode` VARCHAR(6) NOT NULL DEFAULT '',
    `aggGroup` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned aggregation group',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'underlying ticker',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'underlying ticker',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlying ticker',
    `underliersPerCn` INT NOT NULL DEFAULT 0 COMMENT 'number of underlying units per futures contract',
    `underlierType` ENUM('None','Equity','Other','FX') NOT NULL DEFAULT 'None',
    `tickValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single tick change in display premium	(pointValue = tickValue / tickSize)',
    `pointValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single point change in display premium (pointValue = tickValue / tickSize)',
    `pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None' COMMENT 'price display format code',
    `futPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'current future price (any market session) (persists if market closed/halted)',
    `futBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'current future bid (any market session) (zero if market closed/halted)',
    `futAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'current future ask (any market session) (zero if market closed/halted)',
    `futMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'current mark (usually mid market) (freezes at SR CloseMarkTime)',
    `futMarkErrCodes` VARCHAR(255) NOT NULL DEFAULT 'None',
    `futMarkSource` ENUM('None','OpenMark','CloseMark','Print','LiveQuote','QuoteBound') NOT NULL DEFAULT 'None',
    `futOpnMidMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'start of day SR mark',
    `futOpnClrMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'start of day clearing mark (usually exchange settlement mark)',
    `futOpnPosPrv` INT NOT NULL DEFAULT 0 COMMENT 'start-of-day SR contract position (rotated from prior day record)',
    `futOpnPosClr` INT NOT NULL DEFAULT 0 COMMENT 'start-of-day CKR contract position (supplied by client/clearing firm via clearing position load)',
    `futOpnPos` INT NOT NULL DEFAULT 0 COMMENT 'start of period contract position (effective; can be from either CLR or SR)',
    `futOpnPosSrc` ENUM('None','Zero','SR','CLR') NOT NULL DEFAULT 'None' COMMENT 'start of period position source',
    `cnBot` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts bot today',
    `cnSld` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts sld today',
    `cnBotTrd` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts bot today',
    `cnSldTrd` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts sld today',
    `cnBotHdg` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts bot today from open pos hedging (spdrSource=HedgeTool)',
    `cnSldHdg` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts sld today from open pos hedging (spdrSource=HedgeTool)',
    `cnBotTrh` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts bot today from open pos hedging (spdrSource=TradeHedge)',
    `cnSldTrh` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts sld today from open pos hedging (spdrSource=TradeHedge)',
    `cnBotOpn` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts bot today from open pos hedging (spdrSource=OpenHedge)',
    `cnSldOpn` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts sld today from open pos hedging (spdrSource=OpenHedge)',
    `cnOpened` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts opened today',
    `cnClosed` INT NOT NULL DEFAULT 0 COMMENT 'number of contracts closed today',
    `futMnyBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'sum of settle cash for all buy executions',
    `futMnySld` DOUBLE NOT NULL DEFAULT 0 COMMENT 'sum of settle cash for all sell executions',
    `dayPnl` FLOAT NOT NULL DEFAULT 0,
    `opnPnlMidMark` FLOAT NOT NULL DEFAULT 0,
    `opnPnlClrMark` FLOAT NOT NULL DEFAULT 0,
    `beta` FLOAT NOT NULL DEFAULT 0 COMMENT 'beta (usually beta to SPX; see AccountConfig.betaSource) (if applicable)',
    `betaSource` ENUM('None','betaSPX','betaQQQ','betaIWM','clientBeta') NOT NULL DEFAULT 'None',
    `days` SMALLINT NOT NULL DEFAULT 0 COMMENT 'days to expiration',
    `rate` FLOAT NOT NULL DEFAULT 0 COMMENT 'global rate to expiration',
    `years` FLOAT NOT NULL DEFAULT 0 COMMENT 'years to expiration',
    `rh` FLOAT NOT NULL DEFAULT 0 COMMENT 'rho; (dPrc / dRate) - only for STIR futures',
    `ve` FLOAT NOT NULL DEFAULT 0 COMMENT 'vega; (dPrc / dVol) - only for VIX futures',
    `ratePr` FLOAT NOT NULL DEFAULT 0 COMMENT 'start of period global rate',
    `yearsPr` FLOAT NOT NULL DEFAULT 0 COMMENT 'start of period years to expiration',
    `marginUDn` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn',
    `marginUUp` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up',
    `numExecutions` INT NOT NULL DEFAULT 0 COMMENT 'number of included SpdrParentExecution records',
    `maxExecDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'maximum activity dttm of execution records included in this future risk record',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`fkey_tk`,`fkey_yr`,`fkey_mn`,`fkey_dy`,`fkey_at`,`fkey_ts`,`accnt`,`tradeDate`,`riskSession`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='FuturePositionRecords are live risk records that contain start-of-day positions and all subsequent executions, including executions reported as done away.\nThese records are published by a CoreRiskServer and represent the position and risk markup detail for a single futures contract.\nNew records are published immediately when a position changes and about once per minute if no position has changed.\nNote that all stock, future and option records for a chain are published simultaneously and records for the same chain should have consistent marks.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `fkey_at`,
    `fkey_ts`,
    `fkey_tk`,
    `fkey_yr`,
    `fkey_mn`,
    `fkey_dy`,
    `accnt`,
    `tradeDate`,
    `riskSession`,
    `clientFirm`,
    `riskServerCode`,
    `aggGroup`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `underliersPerCn`,
    `underlierType`,
    `tickValue`,
    `pointValue`,
    `pointCurrency`,
    `priceFormat`,
    `futPrc`,
    `futBid`,
    `futAsk`,
    `futMark`,
    `futMarkErrCodes`,
    `futMarkSource`,
    `futOpnMidMark`,
    `futOpnClrMark`,
    `futOpnPosPrv`,
    `futOpnPosClr`,
    `futOpnPos`,
    `futOpnPosSrc`,
    `cnBot`,
    `cnSld`,
    `cnBotTrd`,
    `cnSldTrd`,
    `cnBotHdg`,
    `cnSldHdg`,
    `cnBotTrh`,
    `cnSldTrh`,
    `cnBotOpn`,
    `cnSldOpn`,
    `cnOpened`,
    `cnClosed`,
    `futMnyBot`,
    `futMnySld`,
    `dayPnl`,
    `opnPnlMidMark`,
    `opnPnlClrMark`,
    `beta`,
    `betaSource`,
    `days`,
    `rate`,
    `years`,
    `rh`,
    `ve`,
    `ratePr`,
    `yearsPr`,
    `marginUDn`,
    `marginUUp`,
    `numExecutions`,
    `maxExecDttm`,
    `timestamp`
FROM `SRRisk`.`MsgFuturePositionRecordV5`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `fkey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `fkey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `fkey_tk` = 'Example_fkey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `fkey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `fkey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `fkey_dy` = 1
  AND
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a DATE */
    `tradeDate` = '2022-01-01'
  AND
    /* Replace with a ENUM('Regular','PostClose') */
    `riskSession` = 'Regular'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRRisk.doccolumns WHERE TABLE_NAME='FuturePositionRecordV5' ORDER BY ordinal_position ASC;